﻿namespace Hidistro.SqlDal.Comments
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities;
    using Hidistro.Entities.Comments;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public class HelpDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public bool AddHelp(HelpInfo help)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_Helps(CategoryId, Title, Meta_Description, Meta_Keywords, Description, Content, AddedDate, IsShowFooter) VALUES (@CategoryId, @Title, @Meta_Description, @Meta_Keywords, @Description, @Content, @AddedDate, @IsShowFooter)");
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, help.CategoryId);
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, help.Title);
            this.database.AddInParameter(sqlStringCommand, "Meta_Description", DbType.String, help.MetaDescription);
            this.database.AddInParameter(sqlStringCommand, "Meta_Keywords", DbType.String, help.MetaKeywords);
            this.database.AddInParameter(sqlStringCommand, "Description", DbType.String, help.Description);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, help.Content);
            this.database.AddInParameter(sqlStringCommand, "AddedDate", DbType.DateTime, help.AddedDate);
            this.database.AddInParameter(sqlStringCommand, "IsShowFooter", DbType.Boolean, help.IsShowFooter);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public bool DeleteHelp(int helpId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Helps WHERE HelpId = @HelpId");
            this.database.AddInParameter(sqlStringCommand, "HelpId", DbType.Int32, helpId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public HelpInfo GetFrontOrNextHelp(int helpId, int categoryId, string type)
        {
            string query = string.Empty;
            if (type == "Next")
            {
                query = "SELECT TOP 1 * FROM Hishop_Helps WHERE HelpId <@HelpId AND CategoryId=@CategoryId ORDER BY HelpId DESC";
            }
            else
            {
                query = "SELECT TOP 1 * FROM Hishop_Helps WHERE HelpId >@HelpId AND CategoryId=@CategoryId ORDER BY HelpId ASC";
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            this.database.AddInParameter(sqlStringCommand, "HelpId", DbType.Int32, helpId);
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            HelpInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateHelp(reader);
                }
                reader.Close();
            }
            return info;
        }

        public HelpInfo GetHelp(int helpId)
        {
            HelpInfo info = null;
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Helps WHERE HelpId=@HelpId");
            this.database.AddInParameter(sqlStringCommand, "HelpId", DbType.Int32, helpId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateHelp(reader);
                }
            }
            return info;
        }

        public DbQueryResult GetHelpList(HelpQuery helpQuery)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("Title LIKE '%{0}%'", DataHelper.CleanSearchString(helpQuery.Keywords));
            if (helpQuery.CategoryId.HasValue)
            {
                builder.AppendFormat(" AND CategoryId = {0}", helpQuery.CategoryId.Value);
            }
            if (helpQuery.StartArticleTime.HasValue)
            {
                builder.AppendFormat(" AND AddedDate >= '{0}'", helpQuery.StartArticleTime.Value);
            }
            if (helpQuery.EndArticleTime.HasValue)
            {
                builder.AppendFormat(" AND AddedDate <= '{0}'", helpQuery.EndArticleTime.Value);
            }
            return DataHelper.PagingByTopnotin(helpQuery.PageIndex, helpQuery.PageSize, helpQuery.SortBy, helpQuery.SortOrder, helpQuery.IsCount, "vw_Hishop_Helps", "HelpId", builder.ToString(), "*");
        }

        public DataSet GetHelps()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_HelpCategories WHERE IsShowFooter = 1 ORDER BY DisplaySequence SELECT * FROM Hishop_Helps WHERE IsShowFooter = 1  AND CategoryId IN (SELECT CategoryId FROM Hishop_HelpCategories WHERE IsShowFooter = 1)");
            DataSet set = this.database.ExecuteDataSet(sqlStringCommand);
            DataColumn parentColumn = set.Tables[0].Columns["CateGoryId"];
            DataColumn childColumn = set.Tables[1].Columns["CateGoryId"];
            DataRelation relation = new DataRelation("CateGory", parentColumn, childColumn);
            set.Relations.Add(relation);
            return set;
        }

        public bool UpdateHelp(HelpInfo help)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Helps SET CategoryId = @CategoryId, AddedDate = @AddedDate, Title = @Title, Meta_Description = @Meta_Description, Meta_Keywords = @Meta_Keywords,  Description = @Description, Content = @Content, IsShowFooter = @IsShowFooter WHERE HelpId = @HelpId");
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, help.CategoryId);
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, help.Title);
            this.database.AddInParameter(sqlStringCommand, "Meta_Description", DbType.String, help.MetaDescription);
            this.database.AddInParameter(sqlStringCommand, "Meta_Keywords", DbType.String, help.MetaKeywords);
            this.database.AddInParameter(sqlStringCommand, "Description", DbType.String, help.Description);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, help.Content);
            this.database.AddInParameter(sqlStringCommand, "AddedDate", DbType.DateTime, help.AddedDate);
            this.database.AddInParameter(sqlStringCommand, "IsShowFooter", DbType.Boolean, help.IsShowFooter);
            this.database.AddInParameter(sqlStringCommand, "HelpId", DbType.Int32, help.HelpId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }
    }
}

